﻿CREATE PROCEDURE [maint].[DeleteDbConfig]

AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @RC int, @ERROR int
	SET @RC = 0

	BEGIN TRAN

	DECLARE DB_CUR CURSOR LOCAL FAST_FORWARD FOR(
		SELECT cfg.[DbName]
		FROM [config].[DbMaintenance] AS cfg
		LEFT JOIN master.dbo.sysdatabases AS sdb
			ON (sdb.[name] COLLATE Latin1_General_CI_AS = cfg.[DbName])
		WHERE  (sdb.[name] IS NULL)
	)
	OPEN DB_CUR

	DECLARE @DbName sysname
	FETCH NEXT FROM DB_CUR INTO @DbName
	WHILE(@@FETCH_STATUS = 0)
		BEGIN
			DELETE [config].[DbMaintenance]
			WHERE ([DbName] = @DbName);
			SET @Error = @@ERROR;
			IF(@Error <> 0)
				GOTO ERROR_HANDLER;
				
			PRINT @DbName + ' deleted from maintenance configuration';

			FETCH NEXT FROM DB_CUR INTO @DbName
		END

	IF(@@TRANCOUNT > 0)
		COMMIT TRAN

	GOTO EXIT_PROC

ERROR_HANDLER:
	IF(@@TRANCOUNT > 0)
		ROLLBACK TRAN

	IF(@RC = 0)
		SET @RC = @ERROR
EXIT_PROC:
	RETURN(@RC)
END;